iT邦幫忙

4

[SQL SERVER]-資料庫備份-語法

  • 分享至 

  • xImage
  •  

資料庫常常要做 "完整/差異" 備份,以下的語法是己撰寫好的應用語法,只要填入 JSON 內的資料,就可以做到輕易的資料庫備份應用。我採用的是 "預存程序" + JSON 的方法來達到可以備份資料庫,可以在 JSON 語法中去多做變化,來達到您的需求。

透過 IT邦幫忙 也給我記錄


IF OBJECT_ID('dbo.資料庫備份-快速執行資料庫備份') IS NOT NULL
    DROP PROCEDURE [dbo].[資料庫備份-快速執行資料庫備份]
GO

CREATE PROCEDURE [dbo].[資料庫備份-快速執行資料庫備份]
(
	@_InBox_JSON_Source					nvarchar(Max)		--資料來源
)
AS
		-- 相關注解說明請寫在這裡,以免從 Visual Studio 轉至 SQL 說明內容沒有一起上去
		-- ==========================================================================
		-- 用		途:資料庫備份-完整備份
		-- 製   作  人:
		-- 年   月  日:
		-- 說		明:這部份是給 API 應用
		
		-- 1. 專業人員沒有寫註解不叫專業
		-- 2. 請儘可能 FROM 後面加 WITH(NOLOCK) 不要鎖定表格
		-- 3. 轉 JSON 格式 FOR JSON PATH, (輸出 Null)INCLUDE_NULL_VALUES, (排除 [] 格式)WITHOUT_ARRAY_WRAPPER
		-- 4. 欄位不分大小寫 COLLATE Chinese_Taiwan_Stroke_CI_AS
		-- ==========================================================================
		-- ========宣告系統要用的資訊 - 開始 ================
		DECLARE @_InBox_DataBaseName									nvarchar(100)		=JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name')
		DECLARE @_InBox_BackName       									nvarchar(100)		=JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_BackupFileName')
		DECLARE @_InBox_BackMemo       									nvarchar(100)		=JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_BackupMemo')
		DECLARE @_InBox_BackupMethod       								nvarchar(100)		=JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_BackupMethod')

		DECLARE @_InBox_BackDate										nvarchar(100)		=REPLACE(CONVERT(nvarchar(30),GETDATE(),112),':','');
		DECLARE @_InBox_BackTime										nvarchar(100)		=REPLACE(CONVERT(nvarchar(30),GETDATE(),8),':','');
		DECLARE @_InBox_BackServiceName									nvarchar(100)		=@_InBox_BackName+@_InBox_BackTime
        DECLARE @_InBox_DISK_DB_Path       								nvarchar(500)		=JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory')+@_InBox_BackName+'_'+ @_InBox_BackupMethod +'_'+'DB_'		+@_InBox_BackDate +'_'+@_InBox_BackTime+'.Bak'
		DECLARE @_InBox_DISK_Log_Path									nvarchar(500)		=JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory')+@_InBox_BackName+'_'+ @_InBox_BackupMethod +'_'+'Log_'	+@_InBox_BackDate +'_'+@_InBox_BackTime+'.Bak'
		
		

		--回傳執行上的可能錯誤
		DECLARE @_SystemInfoLog											Table
		(
			DBName														nvarchar(1000),			--資料庫名稱
			DBPathInfo													nvarchar(1000),			--儲存備份路徑
			DBErrorMessage												nvarchar(Max),			--錯誤說明
			Sysinfo_isError												nvarchar(2)	,			--記錄錯誤的訊息
			SysInfo_MainCode											nvarchar(200) ,
			SysInfo_MinorCode											nvarchar(200)
		)
		
		-- ========宣告系統要用的資訊 - 結束 ================
		
		

		if(LEN(@_InBox_DataBaseName)!=0 AND LEN(@_InBox_BackupMethod)!=0)
			Begin
				
				if(@_InBox_BackupMethod='完整')
					Begin
					
					--執行 Try 
					BEGIN TRY  
						--下達完整備份
						BACKUP	DATABASE @_InBox_DataBaseName 
							--========備份磁碟實際上的位置==========
							-- 儲存檔案的位置
							--==========================
							TO DISK = @_InBox_DISK_DB_Path
							--========備份方式==========
							--完整備份 >> WITH NOFORMAT
							--差異備份 >> WITH DIFFERENTIAL
							WITH NOFORMAT, 
							--========壓縮方式==========
							--檔案壓縮 >> COMPRESSION
							--檔案壓縮 >> NO_COMPRESSION 檔案不壓縮
							--==========================
							COMPRESSION ,
							--========備份作業覆寫========
							--備份覆寫 >> INIT
							--附加媒體 >> NOINIT
							--==========================
							NOINIT,
							--========預設資料庫備份或還原的I/O=========
							--預設為 >> 10
							--==============================
							BUFFERCOUNT = 1024,  
							--========此備份資料庫的名稱與說明 255 字以內=========
							--預設為 >> 10
							--==============================
							NAME = @_InBox_BackMemo,
							--========檢查媒體備份到期日====
							--必須要檢查 >> NOSKIP	先檢查媒體中所有備份組的到期日
							--不須要檢查 >> SKIP	則略過檢查動作
							--==============================
							SKIP,
							--=====備份硬體-磁帶備份設定====
							--釋放和倒轉磁帶 >> REWIND		將釋放和倒轉磁帶
							--釋放和倒轉磁帶 >> NOREWIND	不釋放和倒轉磁帶
							--==============================
							NOREWIND, 
							--=====備份硬體-自動倒轉和卸載磁帶====
							--釋放和倒轉磁帶 >> UNLOAD		自動倒轉和卸載磁帶
							--釋放和倒轉磁帶 >> NOUNLOAD	磁帶機上保持載入
							--==============================
							NOUNLOAD,
							--=====完成多少百分比時,回報一則訊息====
							--預設為 >> 10
							--==============================
							STATS = 10

						--填入記錄
						INSERT INTO @_SystemInfoLog VAlues(
							JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name')+'-'+@_InBox_BackupMethod,
							JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory'),
							'執行正常',
							REPLACE(ERROR_STATE(),NULL,'0'),
							'完整備份-成功',
							'資料庫己確定完整備份了 ['+ JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name') +']'
						)
					END TRY  
					BEGIN CATCH  
						
						--填入記錄
						INSERT INTO @_SystemInfoLog VAlues(
							JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name')+'-'+@_InBox_BackupMethod,
							JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory'),
							ERROR_MESSAGE(),
							REPLACE(ERROR_STATE(),NULL,'0'),
							'完整備份-失敗',
							'無法執行資料庫備份 ['+ JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name') +']'
						)

					END CATCH;  


					End
				else if(@_InBox_BackupMethod='差異')
					Begin
						
						--執行 Try 
						BEGIN TRY  

						--下達查異性備份
						BACKUP	DATABASE @_InBox_DataBaseName 
								--========備份磁碟實際上的位置==========
								-- 儲存檔案的位置
								--==========================
								TO DISK = @_InBox_DISK_DB_Path
								--========備份方式==========
								--完整備份 >> WITH NOFORMAT
								--差異備份 >> WITH DIFFERENTIAL
								WITH DIFFERENTIAL, 
								--========壓縮方式==========
								--檔案壓縮 >> COMPRESSION
								--檔案壓縮 >> NO_COMPRESSION    檔案不壓縮
								--==========================
								NO_COMPRESSION ,
								--========備份作業覆寫========
								--備份覆寫 >> INIT
								--附加媒體 >> NOINIT
								--==========================
								NOINIT,
								--========預設資料庫備份或還原的I/O=========
								--預設為 >> 10
								--==============================
								BUFFERCOUNT = 1024,  
								--========此備份資料庫的名稱與說明 255 字以內=========
								--預設為 >> 10
								--==============================
								NAME = @_InBox_BackMemo,
								--========檢查媒體備份到期日====
								--必須要檢查 >> NOSKIP	先檢查媒體中所有備份組的到期日
								--不須要檢查 >> SKIP	則略過檢查動作
								--==============================
								SKIP,
								--=====備份硬體-磁帶備份設定====
								--釋放和倒轉磁帶 >> REWIND		將釋放和倒轉磁帶
								--釋放和倒轉磁帶 >> NOREWIND	不釋放和倒轉磁帶
								--==============================
								NOREWIND, 
								--=====備份硬體-自動倒轉和卸載磁帶====
								--釋放和倒轉磁帶 >> UNLOAD		自動倒轉和卸載磁帶
								--釋放和倒轉磁帶 >> NOUNLOAD	磁帶機上保持載入
								--==============================
								NOUNLOAD,
								--=====完成多少百分比時,回報一則訊息====
								--預設為 >> 10
								--==============================
								STATS = 10
					
							--填入記錄
							INSERT INTO @_SystemInfoLog VAlues(
								JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name')+'-'+@_InBox_BackupMethod,
								JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory'),
								'執行正常',
								REPLACE(ERROR_STATE(),NULL,'0'),
								'差異備份-成功',
								'資料庫己確定差異備份了 ['+ JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name') +']'
							)
						END TRY  
						BEGIN CATCH  
						
							--填入記錄
							INSERT INTO @_SystemInfoLog VAlues(
								JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name')+'-'+@_InBox_BackupMethod,
								JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory'),
								ERROR_MESSAGE(),
								REPLACE(ERROR_STATE(),NULL,'0'),
								'差異備份-失敗',
								'無法執行資料庫差異備份 ['+ JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name') +']'
							)

						END CATCH;  

					End
			End
		else
			Begin
			
				--填入指定的錯誤代碼
				INSERT INTO @_SystemInfoLog VAlues(
					JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Name'),
					JSON_VALUE(@_InBox_JSON_Source,'$[0].DB_Directory'),
					'下達的命令與語法錯誤',
					'0',
					'0',
					'0'
				)
			
			End 

		--轉出整個執行的內容
		SELECT		*
		FROM		@_SystemInfoLog

執行的語法動作

EXEC [資料庫備份-快速執行資料庫備份] '
[  
    {  
        "DB_Name":"資料庫完整的名稱",
        "DB_Directory":"D:\\Microsoft SQL Server\\DataBack\\行政管理\\完整備份\\",
        "DB_BackupFileName":"您要備份的檔案名稱",
        "DB_BackupMemo":"針對此備份你要填入的說明",
        "DB_BackupMethod":"完整/差異"
    }
]'

要注意的部份

  1. 執行時請確定 "執行帳號" 是有權限的
  2. DB_Directory 要儲存的目錄必須自行建制,如果目錄不在就會直接報錯
  3. DB_BackupFileName 是實體檔案名稱
  4. DB_BackupMethod 下達備份方式
     "完整" >> 就會備份完整的資料庫
     "差異" >> 就只備份最後差異性的部份

您可以配合 SQL Server Agent 來做排程來達到需求,備份出來的檔案會像是以下名稱

資料庫名稱_完整_DB_20181219_170916.Bak
資料庫名稱_差異_DB_20181219_170916.bak


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
Luke
iT邦研究生 5 級 ‧ 2019-07-01 19:04:45

請問我按照您的方式做了
出現完整備份,

然後再還原會去就掛了==>一直都顯示還原中
SQL 如下:

restore DATABASE DEMO_DB 
from disk='D:\backup\DEMO_DB_完整__DB_20190701_154052.Bak'
with 
move 'xxx' to 'D:\DemoDB\DMEO_DB.mdf',
move 'xxx_Log' to 'D:\DemoDB\DMEO_DB_log.ldf'
,NoRecovery
,Replace
go

/images/emoticon/emoticon06.gif

看更多先前的回應...收起先前的回應...
PPTaiwan iT邦好手 1 級 ‧ 2019-07-01 19:21:23 檢舉

您選擇還原的選項是什麼呢?? 我剛才直接用 "完整" 再還原到其他資料庫,還原是正常的.. 圖示的備份日期 今天 19:13 分備份再還原,備份失敗那你要看一下無法還原成功的原因是什麼??

你可以直接點選 X 的原因就可以知道了..

https://ithelp.ithome.com.tw/upload/images/20190701/2010485135o5I0E38o.png

Luke iT邦研究生 5 級 ‧ 2019-07-03 14:11:37 檢舉

我是下SQL 指令:

restore DATABASE DEMO_DB 
from disk='D:\backup\DEMO_DB_完整__DB_20190701_154052.Bak'
with 
move 'xxx' to 'D:\DemoDB\DMEO_DB.mdf',
move 'xxx_Log' to 'D:\DemoDB\DMEO_DB_log.ldf'
,NoRecovery
,Replace
go

若是UI 操作的,錯誤

System.Data.SqlClient.SqlError: 備份組包含現有的 'OEE' 資料庫以外的資料庫備份。 (Microsoft.SqlServer.Express.Smo)
TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'OEE' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'OEE' database. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Luke iT邦研究生 5 級 ‧ 2019-07-03 14:26:00 檢舉

/images/emoticon/emoticon01.gif
我將NoRecovery 改成 Recovery 就好了
原來NoRecovery 是還要繼續還原...

restore DATABASE OEE 
from disk='D:\DataBack\OEE\OEE_完整__DB_20190701_154052.Bak' 
with 
move 'OEE' to 'D:\OEE_DB\OEE.mdf',
move 'OEE_log' to 'D:\OEE_DB\OEE_log.ldf'
,Recovery
,Replace
go
PPTaiwan iT邦好手 1 級 ‧ 2019-07-03 15:44:18 檢舉

喔...了解..

0
Luke
iT邦研究生 5 級 ‧ 2019-07-03 15:41:07

請問(星期一)做了完整備份 => OEE_完整DB_20190701_154052.Bak
今天(星期三)做了一個差異備份 => OEE_差異__DB_20190703_144614.Bak

上午試了完整備份。正常也沒有問題
那我怎樣差異備份,補上資料庫?

PPTaiwan iT邦好手 1 級 ‧ 2019-07-03 18:24:20 檢舉

那你要看有沒有啟動 SQL Server Agent 呢?? 基本上我只要設定好,他就會自動備份 "完整與差異" 的動作

0
Luke
iT邦研究生 5 級 ‧ 2019-07-05 18:19:10

有個問題

0704做完整Backup
0705在做差異Backup
差異檔案比完整檔案 大?
在做個交易 更大?/images/emoticon/emoticon06.gif
https://ithelp.ithome.com.tw/upload/images/20190705/20096781pPB3RicBsc.png

有看了:https://blog.miniasp.com/post/2010/04/21/SQL-Server-Full-Differential-Transaction-Backup
觀念釐清:SQL Server 完整備份、差異備份、交易記錄備份

PPTaiwan iT邦好手 1 級 ‧ 2019-07-05 18:42:18 檢舉

基本上,您的備份應該不太對!! 以我備份的經驗會是如下...

完整_DB_20190705_000001.Bak
差異_DB_20190705_003000.Bak
差異_DB_20190705_010000.Bak
差異_DB_20190705_010300.Bak

每天晚上 23:59:59 分鐘執行完整備份,每 30 分鐘差異性備份,所以我的檔案大概就會如上面的清單所列出來的,基本上如您並沒有設定連續的差異性備份檔案,那您可能要先看看您的設定有沒有錯誤了...

保哥的備份文章是 "理解備份觀念" ,但您的產生備份應該是設定上有錯誤...您再多試試...

Luke iT邦研究生 5 級 ‧ 2019-07-08 09:11:04 檢舉

所以不做交易備份

我想
星期天 : 完成備份
星期一 ~ 星期六: 每天做差異備份
每四小時做交易備份

PPTaiwan iT邦好手 1 級 ‧ 2019-07-08 11:32:26 檢舉

喔...我是不太建議啦!! 完整備份我會每一天做一次,並且用外寫一支程式來對這些檔案進行 "以日期條件" 進行壓縮,那檔案的 Size 容量差很多。

以我的經驗,我的方法是比較保守一點。因為還原 "當天" 的備份必須要連同 "完整備份" 也要一併進行還原(你可以參考保哥的文章) 裡面有一段 "假設你想將資料庫還原到 2010/4/21 PM 9:00 的版本" 。還原資料會接續前面的備份,如果以你的方式,那你要將 "所有的備份檔案" 做為還原的檔案,那個數量真的是會差很多。

而且還原的時間會大一倍的時間,因為 "我認為以你的備份數量要還原到特定日期" 的資料,我會懷疑還原的回去嗎?? 每天一次的完整/差異備份就是提供在 "特定日期與時間內還原資料" 。 我是沒有做過以你的備份並還原的動作過,不然你自己試看看在不同的條件之下 "這樣子的備份是否符合你的需求"。

那我還覺得這樣子備份,還不如用 "資料庫鏡像和複寫 (SQL Server)" 的方法來處理,多複製一份資料到另一個資料庫,那可以做的事情更多..

我要留言

立即登入留言